Using Excel Spreadsheets in Physics Teaching

A. INTRODUCTION

For many candidate physics teachers spreadsheets are a new experience in exploring physics. Spreadsheets will help you and your students investigate phenomena in a way that is very different from traditional problem solving. The word "investigate" is used here to convey the exploratory nature of the activities which you will encounter in this project. In this project you will learn how to use EXCEL as a tool to explore the behavior of physical phenomena. The nature of these explorations is sufficiently different from normal physics problems that you will encounter several surprises and interesting twists. The purpose of this project is two-fold: to provide you with a setting to explore physics that is not constrained by the need to use advanced mathematics, and to introduce you to a powerful tool for exploring the world of physics. Additionally, you will use your spreadsheet skills to create a small grade book program.

IMPORTANT NOTICE: Many students choose to skip working through the tutorial thinking that it will take no time at all to just begin working successfully with this software. This has proven not to be the case. PLEASE, for your own sake, work through the tutorial. In the long run you'll spend a lot less time on this project. If you are more facile with another program, you may use that to create your spread sheet; there is nothing sacred about Excel. Any spreadsheet software will do; but you need to be able to demonstrate the working of your spreadsheet. Please note that the department has one accessible Windows PC that might be used for demonstration purposes only IF you bring along the proper software and the diskette used is known to be virus free.

Familiarize yourself with the power of Excel in both Physics and Mathematics by working with each of the following four examples: vector analysis, projectile motion, solving simultaneous quadratic equations graphically, and using the bisection method. These four examples were written by Dr. Rob Mason at Olney Central College (Olney, IL) and are used here with his permission.

B. PROCEDURE

If you are not familiar with Excel, sit down for an orientation with your course instructor. A user's guide is also available from the instructor if you need further assistance. If you are already familiar with Excel and/or spreadsheet applications, continue on to step 1.

1. You will need to do ONE the following activities (in addition to that below) to demonstrate your knowledge of Excel or a similar spreadsheet application:

• 1(a) Prepare a spreadsheet that calculates the range of a projectile given the launch angle, initial speed, and acceleration due to gravity. Students should be able to vary launch angle, initial speed, and acceleration due to gravity to see what effects each of these has on the range.
• 1(b) Prepare a spreadsheet that calculates the angle of refraction given glass in air with an index of refraction and an angle of incidence that is variable.
• 1(c) Prepare a spreadsheet that calculates the current flow in a series circuit (dc power supply, resistor, light bulb) given variable resistance, a variable dc power supply, and a light bulb with an assumed-constant 20 ohms of resistance. Calculate the radiative power of the light bulb in watts.

2. You will need to do two of the following activities (in addition to parts 3 and 4 below) to demonstrate your knowledge of Excel or a similar spreadsheet application:

• (2a) Using a suitable data set, create a graph showing a linear fit to data using (if necessary) StatPlus along with Excel on the computers in MLT 307-B if necessary.
• (2b) Create a data set of numbers ranging from 0 to 50. Create a histogram using (if necessary) StatPlus along with Excel on the computers in MLT 307-B if necessary.
• (2c) Create a surface (contour) plot to show lines of equipotential in the following E-field strength data:

3. Create a grade book program that satisfies the following characteristics:

• Contains the "names" of 24 students (e.g., S1, S2, S3 etc.)
• Contains five RANDOM test scores (e.g. points) for each student with a range of 0 to 100. Arrange these scores as rows, the right of each students name. HINT: Use the =RANDBETWEEN function in the format =RANDBETWEEN(0,100).
• Attempt to generate random scores with a mean of about 75% if possible.
• Below each column showing test scores, have the spreadsheet calculate the mean, median, and standard deviation for each test.
• For individual students, have the program calculate the mean percentage for all test scores to the right of the data.
• Using mean percentages for all students, have the program determine a course letter grade using 90%, 80%, 70% and 60% cut offs for grades of A, B, C, and D respectively. Hint: Use nested conditional statements to do this. For example, if cell G3 is first used to determine the score of a student, then G3 should contain the following statement: =IF(G3>89,"A", IF(G3>79, "B",IF(G3>69, "C", IF(G3>59, "D", "F"))))
• Create a histogram with cells 10 points wide based upon each student's composite score for the course; count the number in ranges from 100-90, 89.99-80, etc. Hint: Histogram creation is done using the menu commands TOOLS:DATA ANALYSIS...:HISTOGRAM. If DATA ANALYSIS... is not available, you might be able to install the appropriate software by accessing TOOLS:ADD-INS...:ANALYSIS TOOLPAK. Once the data for the histogram is tabulated, use the Chart Wizard to create the actual histogram.

4. Use a linear correlation (Pearson) along with StatPlus (if necessary) and Excel to see if the following pre-test and post-test scores are linearly related to one another. Compare two sets of test scores for the same students using correlation analysis. The data below are for a pre-test and a post-test. Be certain to report all statistics that StatPlus reports.

C. EVALUATION

Your instructor will examine each spreadsheet for accuracy and workability. The student should be prepared to demonstrate and answer questions about each spreadsheet as indicated by the instructor.

Return to PHY 302 Syllabus COURSE CONTENT Section

(Last updated 10/13/2010, cjw)